﻿using FTBS.Models.Entities;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;

namespace FTBS.Models.DAO
{
    public class CategoryDAO
    {
        private FilmTicketBookingSystemEntities _entities = new FilmTicketBookingSystemEntities();

        /// <summary>
        /// Load all category
        /// </summary>
        /// <returns>List of Category</returns>
        /// <author>VuongVT</author>
        public List<Category> LoadCategories()
        {
            List<Category> listCategory = new List<Category>();
            try
            {
                var categories = from category in _entities.Categories
                                 where category.IsDelete == false
                                 select category;
                if (categories != null)
                {
                    listCategory = categories.ToList();
                }
            }
            catch
            {
                return listCategory;
            }
            return listCategory;
        }

        /// <summary>
        /// Load Category By Id
        /// </summary>
        /// <returns>Category</returns>
        /// <author>VuongVT</author>
        public Category LoadCategoryById(int id)
        {
            Category category = new Category();
            try
            {
                category = (from categoryEnt in _entities.Categories
                        where categoryEnt.CategoryId == id
                             && categoryEnt.IsDelete == false
                        select categoryEnt).FirstOrDefault();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return category;
        }

        /// <summary>
        /// Add a Category
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Add(Category category)
        {
            try
            {
                _entities.Categories.Add(category);
                int resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// Edit a Category
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Edit(Category category)
        {
            try
            {
                _entities.Entry(category).State = EntityState.Modified;
                int resultQuery = _entities.SaveChanges();
                return resultQuery;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// Delete a Category
        /// </summary>
        /// <returns>1: Success -- 0: Fail</returns>
        /// <author>VuongVT</author>
        public int Delete(Category category)
        {
            try
            {
                category.IsDelete = true;
                _entities.Entry(category).State = EntityState.Modified;
                int result = _entities.SaveChanges();
                return result;
            }
            catch (Exception ex)
            {
                return 0;
                throw ex;
            }
        }

        /// <summary>
        /// check exist category
        /// </summary>
        /// <param name="category"></param>
        /// <returns>true: existed; false: not existed</returns>
        /// <author>hungph</author>
        public bool CheckIsExisted(Category category)
        {
            try
            {
                var isExist = _entities.Categories.Any(c => c.CategoryName == category.CategoryName && c.IsDelete == false);
                return isExist;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public Category GetCategoryById(int id)
        {
            var category = _entities.Categories.Find(id);
            return category;
        }

        /// <summary>
        /// get the most favorite category of user
        /// </summary>
        /// <param name="un"></param>
        /// <returns>cateId</returns>
        /// <author>DuyHDV</author>
        public int GetCategoryByUsername(string un)
        {
            var films = (
                from f in _entities.Films
                from s in _entities.Schedules
                from c in _entities.Categories
                from t in _entities.Tickets
                from tc in _entities.TicketCarts
                where
                    tc.UserName == un &&
                    t.CartId == tc.CartId &&
                    s.ScheduleId == t.ScheduleId &&
                    f.FilmId == s.FilmId &&
                    c.CategoryId == f.CategoryId
                select f
                ).ToList();
            if (films.Count>0)
            {
                var mostCategory =
                    (from m in films group m by m.CategoryId into g orderby g.Count() descending select g).First();
                return mostCategory.Key;
            }
            else
            {
                return 0;
            }
        }

        /// <summary>
        /// get number of film by category
        /// </summary>
        /// <param name="categoryId"></param>
        /// <returns>number of film by category</returns>
        public int GetNumbersOfFilmByCategory(int categoryId)
        {
            var count = _entities.Films.Count(film => film.CategoryId == categoryId && film.IsDeleted == false);
            return count;
        }
    }
}